/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package eu.easyedu.dictionary.api.util;

import eu.easyedu.dictionary.api.model.Dictionary;
import eu.easyedu.dictionary.api.options.DictionaryOptions;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.netbeans.api.db.explorer.ConnectionManager;
import org.netbeans.api.db.explorer.DatabaseConnection;
import org.netbeans.api.db.explorer.JDBCDriver;
import org.netbeans.api.db.explorer.JDBCDriverManager;
import org.openide.util.Exceptions;

/**
 *
 * @author hlavki
 */
public class DatabaseUtils {

    private static final Logger log = Logger.getLogger(DatabaseUtils.class.getName());
    private static final Map<Dictionary, DictionaryConnection> connectionCache =
            new HashMap<Dictionary, DictionaryConnection>();
    private static final String TABLE_NAME_PROPERTY = "TABLE_NAME";
    private static final Set<String> TABLE_NAMES = new HashSet<String>();

    static {
        TABLE_NAMES.add("WORD");
        TABLE_NAMES.add("LANGUAGE");
        TABLE_NAMES.add("WORD_RELATIONSHIP");
    }

    public static DictionaryConnection getDefaultConnection() throws ConnectionDoesNotExistsException {
        return getConnection(DictionaryOptions.getInstance().getDefaultDictionary());
    }

    public static DictionaryConnection getConnection(Dictionary dict) throws ConnectionDoesNotExistsException {
        if (dict == null) {
            return null;
        }
        DictionaryConnection result = null;
        String connectionName = dict.getConnectionName();
        synchronized (connectionCache) {
            result = connectionCache.get(dict);
            if (result == null) {
                DatabaseConnection dbConn = ConnectionManager.getDefault().getConnection(connectionName);
                if (dbConn != null) {
                    result = new DictionaryConnection(dbConn);
                    connectionCache.put(dict, result);
                } else {
                    throw new ConnectionDoesNotExistsException("Connection " + connectionName + " does not exists!");
                }
            }
        }
        log.fine("Using database connection: " + result.toString());
        return result;
    }

    public static void removeConnection(Dictionary dict) {
        synchronized (connectionCache) {
            DictionaryConnection conn = connectionCache.get(dict);
            if (conn != null) {
                conn.remove();
                connectionCache.remove(dict);
            }
        }
    }

    public static String[] getSupportedConnectionNames() {
        DatabaseConnection[] conns = ConnectionManager.getDefault().getConnections();
        ArrayList<String> connNames = new ArrayList<String>();
        for (int idx = 0; idx < conns.length; idx++) {
            if (conns[idx].getDriverClass().equals("org.apache.derby.jdbc.ClientDriver") ||
                    conns[idx].getDriverClass().equals("org.apache.derby.jdbc.EmbeddedDriver")) {
                connNames.add(conns[idx].getName());
            }
        }
        return connNames.toArray(new String[]{});
    }

    public static boolean checkIfConnectionContainsDictionary(Dictionary dict) throws ConnectionDoesNotExistsException {
        boolean result = true;
        try {
            DictionaryConnection dbConn = getConnection(dict);
            Connection conn = dbConn.getJDBCConnection();
            DatabaseMetaData metaData = conn.getMetaData();
            Set<String> tableSet = new HashSet<String>(TABLE_NAMES);
            ResultSet rs = metaData.getTables(null, dbConn.getSchema(), null, null);
            while (rs.next()) {
                String tableName = rs.getString(TABLE_NAME_PROPERTY);
                tableSet.remove(tableName);
            }
            result = tableSet.isEmpty();
        } catch (SQLException e) {
            result = false;
            log.log(Level.SEVERE, e.getMessage(), e);
        }
        return result;
    }

    public static boolean checkIfConnectionContainsDictionary() throws ConnectionDoesNotExistsException {
        return checkIfConnectionContainsDictionary(DictionaryOptions.getInstance().getDefaultDictionary());
    }

    public static JDBCDriver getDriver(String driverClass) {
        for (JDBCDriver driver : JDBCDriverManager.getDefault().getDrivers()) {
            if (driverClass.equals(driver.getClassName())) {
                return driver;
            }
        }
        return null;
    }

    public static boolean containsSupportedConnection() {
        return getSupportedConnectionNames().length > 0;
    }

    public static void addConnectionEventListener(Dictionary dict, ConnectionEventListener listener) {
        log.info("Registering connection event listener for dictionary " + dict.getName());
        try {
            DatabaseUtils.getConnection(dict).addConnectionEventListener(listener);
        } catch (ConnectionDoesNotExistsException ex) {
            Exceptions.printStackTrace(ex);
        }
    }

    public static void removeConnectionEventListener(Dictionary dict, ConnectionEventListener listener) {
        log.info("Removing connection event listener for dictionary " + dict.getName());
        try {
            DatabaseUtils.getConnection(dict).removeConnectionEventListener(listener);
        } catch (ConnectionDoesNotExistsException ex) {
            Exceptions.printStackTrace(ex);
        }
    }

    public static void startTransaction(Dictionary dict) throws ConnectionDoesNotExistsException, SQLException {
        DictionaryConnection dictConn = getConnection(dict);
        if (dictConn != null) {
            Connection conn = dictConn.getJDBCConnection();
            conn.setAutoCommit(false);
        }
    }

    public static void commitTransaction(Dictionary dict) throws ConnectionDoesNotExistsException, SQLException {
        DictionaryConnection dictConn = getConnection(dict);
        if (dictConn != null) {
            Connection conn = dictConn.getJDBCConnection();
            conn.commit();
            conn.setAutoCommit(true);
        }
    }

    public static void rollbackTransaction(Dictionary dict) throws ConnectionDoesNotExistsException, SQLException {
        DictionaryConnection dictConn = getConnection(dict);
        if (dictConn != null) {
            Connection conn = dictConn.getJDBCConnection();
            conn.rollback();
            conn.setAutoCommit(true);
        }
    }
}
